﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interfaces;
using System.Data.SqlClient;
using Business.Entities.Model;
using System.Drawing;
using System.IO;
using System.Data;

namespace Business.Entities.DB
{

    public class ApplicantDBManager : IApplicantDBManager
    {
        private DataAccessLayer dal;

        public ApplicantDBManager()
        {
            dal = new DataAccessLayer();

        }

        /// <summary>
        /// Create a new Applicant in the DB
        /// </summary>
        /// <param name="_Applicant"></param>
        /// <returns></returns>
        public int CreateApplicant(IApplicant _Applicant)
        {
            dal.SetQuery("INSERT  INTO [applicant_info] ([address], [city], [date_of_birth], [email], [first_name], [kot_cpr], [last_login], [last_name], [sex], [telephone_number], [user_id], [zipcode], [country], [picture], [Citizenship], [international_office_comments]) VALUES (@Address, @City, @Date_of_birth, @Email, @First_name, @Kot_cpr, @Last_login, @Last_name, @Sex, @Telephone_number, @User_id, @Zipcode, @Country, @Picture, @Citizenship, @UCN_comments)");

            dal.AddParameter("@Address", _Applicant.Address, System.Data.DbType.String);
            dal.AddParameter("@City", _Applicant.City, System.Data.DbType.String);
            dal.AddParameter("@Date_of_birth", _Applicant.Date_of_birth, System.Data.DbType.DateTime);
            dal.AddParameter("@Email", _Applicant.Email, System.Data.DbType.String);
            dal.AddParameter("@First_name", _Applicant.First_name, System.Data.DbType.String);
            dal.AddParameter("@Kot_cpr", _Applicant.Kot_cpr, System.Data.DbType.String);
            dal.AddParameter("@Last_login", _Applicant.Last_login, System.Data.DbType.DateTime);
            dal.AddParameter("@Last_name", _Applicant.Last_name, System.Data.DbType.String);
            dal.AddParameter("@Sex", _Applicant.Sex, System.Data.DbType.String);
            dal.AddParameter("@Telephone_number", _Applicant.Telephone_number, System.Data.DbType.String);
            dal.AddParameter("@Citizenship", _Applicant.Citizenship, System.Data.DbType.String);
            dal.AddParameter("@User_id", _Applicant.User_id, System.Data.DbType.String);
            dal.AddParameter("@Zipcode", _Applicant.Zipcode, System.Data.DbType.String);
            dal.AddParameter("@Country", _Applicant.Country, System.Data.DbType.String);
            dal.AddParameter("@UCN_comments", _Applicant.UCN_comments, DbType.String);
            if (_Applicant.Picture != null)
            {
                dal.AddParameter("@Picture", _Applicant.Picture, SqlDbType.VarBinary, int.MaxValue);
            }
            else
            {
                dal.AddParameter("@Picture", DBNull.Value, SqlDbType.VarBinary, int.MaxValue);
            }

            return dal.Execute();
        }
        /// <summary>
        /// Update the applicants information in the DB
        /// </summary>
        /// <param name="_Applicant"></param>
        public int UpdateApplicant(IApplicant _Applicant)
        {
            dal.SetQuery("UPDATE [applicant_info] SET [address] = @Address, [city] = @City, [date_of_birth] = @Date_of_birth, [email] = @Email, [first_name] = @First_name, [kot_cpr] = @Kot_cpr, [last_login] = @Last_login, [last_name] = @Last_name, [sex] = @Sex, [telephone_number] = @Telephone_number, [user_id] = @User_id, [zipcode] = @Zipcode, [country] = @Country, [citizenship] = @Citizenship, [international_office_comments] = @UCN_comments, [has_paid_student_fee] = @has_paid_student_fee WHERE user_id = @User_id");

            dal.AddParameter("@Address", _Applicant.Address, System.Data.DbType.String);
            dal.AddParameter("@City", _Applicant.City, System.Data.DbType.String);
            dal.AddParameter("@Date_of_birth", _Applicant.Date_of_birth, System.Data.DbType.DateTime);
            dal.AddParameter("@Email", _Applicant.Email, System.Data.DbType.String);
            dal.AddParameter("@First_name", _Applicant.First_name, System.Data.DbType.String);
            dal.AddParameter("@Kot_cpr", _Applicant.Kot_cpr, System.Data.DbType.String);
            dal.AddParameter("@Last_login", _Applicant.Last_login, System.Data.DbType.DateTime);
            dal.AddParameter("@Last_name", _Applicant.Last_name, System.Data.DbType.String);
            dal.AddParameter("@Sex", _Applicant.Sex, System.Data.DbType.String);
            dal.AddParameter("@Telephone_number", _Applicant.Telephone_number, System.Data.DbType.String);
            dal.AddParameter("@User_id", _Applicant.User_id, System.Data.DbType.String);
            dal.AddParameter("@Zipcode", _Applicant.Zipcode, System.Data.DbType.String);
            dal.AddParameter("@Country", _Applicant.Country, System.Data.DbType.String);
            dal.AddParameter("@Citizenship", _Applicant.Citizenship, DbType.String);
            dal.AddParameter("@UCN_comments", _Applicant.UCN_comments, DbType.String);
            dal.AddParameter("@has_paid_student_fee", _Applicant.Has_paid_student_fee, DbType.Boolean);
            //   dal.AddParameter("@Picture", _Applicant.Picture, SqlDbType.VarBinary, int.MaxValue); //There is a seperate method for updating the picture, which should be used (if this is line is active again later remember to insert -> [picture] = @Picture in the query!

            return dal.Execute();
        }
        /// <summary>
        /// Gets applicant by their User_id, complete with accomodation and arrival information
        /// </summary>
        /// <param name="_User_id"></param>
        /// <returns></returns>
        public IApplicant GetApplicantByUserId(string _User_id)
        {
            IApplicant applicant = null;
            IApplication application = null;

            dal.SetQuery("SELECT * FROM [applicant_info] LEFT JOIN [accomodation] ON ([applicant_info].user_id = [accomodation].user_id) LEFT JOIN [arrival] ON ([applicant_info].user_id = [arrival].user_id) LEFT JOIN [application] on [applicant_info].user_id = [application].user_id WHERE [applicant_info].user_id = @User_id");
            dal.AddParameter("@User_id", _User_id, System.Data.DbType.String);
            SqlDataReader reader = dal.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    application = null;
                    string temp = reader["status"].ToString();

                    if (!temp.Equals(""))
                    {
                        application = ReadApplication(reader);
                    }

                    if (applicant == null)
                    {
                        applicant = ReadApplicant(reader);

                        applicant.Aplications = new List<IApplication>();
                        if (application != null)
                        {
                            applicant.Aplications.Add(application);
                        }
                    }
                    else
                    {
                        if (application != null)
                        {
                            applicant.Aplications.Add(application);
                        }
                    }
                }
            }
            else
            {
                return null;
            }
            return applicant;
        }

        /// <summary>
        /// Return all Applicants sorted by Country
        /// </summary>
        /// <returns></returns>
        public IList<IApplicant> GetAllApplicants()
        {
            IList<IApplicant> result = new List<IApplicant>();
            IApplicant applicant = null;
            IApplication application = null;
            int listTgt = -1;

            dal.SetQuery("SELECT * FROM [applicant_info] LEFT JOIN [accomodation] ON ([applicant_info].user_id = [accomodation].user_id) LEFT JOIN [arrival] ON ([applicant_info].user_id = [arrival].user_id) LEFT JOIN [application] on [applicant_info].user_id = [application].user_id");
            //dal.AddParameter("@column", _Column, System.Data.DbType.String);
            SqlDataReader reader = dal.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    application = null;
                    string temp = reader["status"].ToString();

                    if (!temp.Equals(""))
                    {
                        application = ReadApplication(reader);
                    }

                    listTgt = -1;
                    temp = reader["user_id"].ToString();
                    int c = result.Count;
                    for (int i = 0; i < c; i++)
                    {
                        if (result[i].User_id.Equals(temp))
                        {
                            listTgt = i;
                        }
                    }

                    if (listTgt == -1)
                    {
                        applicant = ReadApplicant(reader);

                        applicant.Aplications = new List<IApplication>();
                        if (application != null)
                        {
                            applicant.Aplications.Add(application);
                        }

                        result.Add(applicant);
                    }
                    else
                    {
                        if (application != null)
                        {
                            result[listTgt].Aplications.Add(application);
                        }
                    }
                }
            }
            else
            {
                return null;
            }
            return result;
        }

        public IList<IApplicant> GetApplicantsByStatus(string _Status)
        {
            IList<IApplicant> result = new List<IApplicant>();
            IApplicant applicant = null;
            IApplication application = null;
            int listTgt = -1;

            dal.SetQuery("SELECT * FROM [applicant_info] LEFT JOIN [accomodation] ON ([applicant_info].user_id = [accomodation].user_id) LEFT JOIN [arrival] ON ([applicant_info].user_id = [arrival].user_id) LEFT JOIN [application] on [applicant_info].user_id = [application].user_id WHERE [applicant_info].user_id IN (SELECT user_id FROM [application] WHERE [application].status = @status);");
            dal.AddParameter("@status", _Status, System.Data.DbType.String);
            SqlDataReader reader = dal.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    application = null;
                    string temp = reader["status"].ToString();

                    if (!temp.Equals(""))
                    {
                        application = ReadApplication(reader);
                    }

                    listTgt = -1;
                    temp = reader["user_id"].ToString();
                    int c = result.Count;
                    for (int i = 0; i < c; i++)
                    {
                        if (result[i].User_id.Equals(temp))
                        {
                            listTgt = i;
                        }
                    }

                    if (listTgt == -1)
                    {
                        applicant = ReadApplicant(reader);

                        applicant.Aplications = new List<IApplication>();
                        if (application != null)
                        {
                            applicant.Aplications.Add(application);
                        }

                        result.Add(applicant);
                    }
                    else
                    {
                        if (application != null)
                        {
                            result[listTgt].Aplications.Add(application);
                        }
                    }
                }
            }
            else
            {
                return null;
            }
            return result;
        }

        /// <summary>
        /// Creates the accomodation information in the DB
        /// </summary>
        /// <param name="_Accomodation"></param>
        /// <param name="_User_id"></param>
        /// <returns></returns>
        public int CreateAccomodation(IAccomodation _Accomodation, string _User_id)
        {
            dal.SetQuery("INSERT  INTO [accomodation] ([accomodation_start], [accomodation_end], [actual_accomodation], [comments_and_wishes], [user_id]) VALUES (@Accomodation_start, @Accomodation_end, @Actual_accomodation, @Comments_and_wishes, @User_id)");

            dal.AddParameter("@Accomodation_start", _Accomodation.Accomodation_start, System.Data.DbType.DateTime);
            dal.AddParameter("@Accomodation_end", _Accomodation.Accomodation_end, System.Data.DbType.DateTime);
            dal.AddParameter("@Actual_accomodation", _Accomodation.Actual_accomodation, System.Data.DbType.String);
            dal.AddParameter("@Comments_and_wishes", _Accomodation.Comments_and_wishes, System.Data.DbType.String);
            dal.AddParameter("@User_id", _User_id, System.Data.DbType.String);

            return dal.Execute();
        }

        /// <summary>
        /// Creates the arrival information in the DB
        /// </summary>
        /// <param name="_Arrival"></param>
        /// <param name="_User_id"></param>
        /// <returns></returns>
        public int CreateArrival(IArrival _Arrival, string _User_id)
        {
            dal.SetQuery("INSERT  INTO [arrival] ([arrival_by], [time_of_arrival], [flight_number], [user_id]) VALUES (@arrival_by, @time_of_arrival, @flight_number, @user_id)");

            dal.AddParameter("@arrival_by", _Arrival.Method_of_transportation.ToString(), System.Data.DbType.String);
            dal.AddParameter("@time_of_arrival", _Arrival.Time_of_arrival, System.Data.DbType.DateTime);
            dal.AddParameter("@flight_number", _Arrival.Flight_number, System.Data.DbType.String);
            dal.AddParameter("@user_id", _User_id, System.Data.DbType.String);

            return dal.Execute();
        }
        /// <summary>
        /// Updates the arrival information in the DB
        /// </summary>
        /// <param name="_Arrival"></param>
        /// <param name="_User_id"></param>
        /// <returns></returns>
        public int UpdateArrival(IArrival _Arrival, string _User_id)
        {
            dal.SetQuery("UPDATE [arrival] SET [arrival_by] = @Arrival_by, [time_of_arrival] = @Time_of_arrival, [flight_number] = @Flight_number WHERE user_id = @User_id");

            dal.AddParameter("@Arrival_by", _Arrival.Method_of_transportation.ToString(), System.Data.DbType.String);
            dal.AddParameter("@Time_of_arrival", _Arrival.Time_of_arrival, System.Data.DbType.DateTime);
            dal.AddParameter("@Flight_number", _Arrival.Flight_number, System.Data.DbType.String);
            dal.AddParameter("@User_id", _User_id, System.Data.DbType.String);

            return dal.Execute();
        }
        /// <summary>
        /// Updates the accomodation information in the DB
        /// </summary>
        /// <param name="_Accomodation"></param>
        /// <param name="_User_id"></param>
        /// <returns></returns>
        public int UpdateAccomodation(IAccomodation _Accomodation, string _User_id)
        {
            dal.SetQuery("UPDATE [accomodation] SET [accomodation_start] = @Accomodation_start, [accomodation_end] = @Accomodation_end, [actual_accomodation] = @Actual_accomodation, [comments_and_wishes] = @Comments_and_wishes WHERE user_id = @User_id");

            dal.AddParameter("@Accomodation_start", _Accomodation.Accomodation_start, System.Data.DbType.DateTime);
            dal.AddParameter("@Accomodation_end", _Accomodation.Accomodation_end, System.Data.DbType.DateTime);
            dal.AddParameter("@Actual_accomodation", _Accomodation.Actual_accomodation, System.Data.DbType.String);
            dal.AddParameter("@Comments_and_wishes", _Accomodation.Comments_and_wishes, System.Data.DbType.String);
            dal.AddParameter("@User_id", _User_id, System.Data.DbType.String);

            return dal.Execute();
        }


        public int DeleteAccomodation(string _User_id)
        {
            dal.SetQuery("DELETE FROM [accomodation] WHERE [accomodation].user_id = @User_id");
            dal.AddParameter("@User_id", _User_id, System.Data.DbType.String);
            return dal.Execute();
        }

        public int DeleteArrival(string _User_id)
        {
            dal.SetQuery("DELETE FROM [arrival] WHERE [arrival].user_id = @User_id");
            dal.AddParameter("@User_id", _User_id, System.Data.DbType.String);
            return dal.Execute();
        }

        public int DeleteApplicant(string _User_id)
        {
            DeleteAccomodation(_User_id);
            DeleteArrival(_User_id);
            dal.SetQuery("DELETE FROM [applicant_info] WHERE [applicant_info].user_id = @User_id");
            dal.AddParameter("@User_id", _User_id, System.Data.DbType.String);
            return dal.Execute();
        }

        public IAccomodation GetAccomodationByUserId(string _User_id)
        {
            IAccomodation accomodation = new Accomodation();
            dal.SetQuery("SELECT * FROM [accomodation] WHERE user_id = @User_id");
            dal.AddParameter("@User_id", _User_id, System.Data.DbType.String);

            SqlDataReader reader = dal.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {

                    if (reader["accomodation_end"].ToString() == "")
                    {
                        accomodation.Accomodation_end = null;
                    }
                    else
                    {
                        accomodation.Accomodation_end = Convert.ToDateTime(reader["accomodation_end"].ToString());
                    }
                    if (reader["accomodation_start"].ToString() == "")
                    {
                        accomodation.Accomodation_start = null;
                    }
                    else
                    {
                        accomodation.Accomodation_start = Convert.ToDateTime(reader["accomodation_start"].ToString());
                    }
                    accomodation.Actual_accomodation = reader["actual_accomodation"].ToString();
                    accomodation.Comments_and_wishes = reader["comments_and_wishes"].ToString();
                }
            }
            else
            {
                return null;
            }
            return accomodation;

        }

        public IArrival GetArrivalByUserId(string _User_id)
        {
            IArrival arrival = new Arrival();
            dal.SetQuery("SELECT * FROM [arrival] WHERE user_id = @User_id");
            dal.AddParameter("@User_id", _User_id, System.Data.DbType.String);
            SqlDataReader reader = dal.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {

                    arrival.Flight_number = reader["flight_number"].ToString();
                    if (reader["arrival_by"].ToString() == "")
                    {
                        arrival.Method_of_transportation = null;
                    }
                    else
                    {
                        arrival.Method_of_transportation = (Arrival_method)Enum.Parse(typeof(Arrival_method), reader["arrival_by"].ToString());
                    }
                    if (reader["time_of_arrival"].ToString() == "")
                    {
                        arrival.Time_of_arrival = null;
                    }
                }
            }
            else
            {
                return null;
            }
            return arrival;
        }

        public int CreateApplication(IApplication _Application)
        {
            dal.SetQuery("INSERT INTO [application] ([user_id], [status], [last_activity], [progress], [created], [agent_id]) VALUES (@User_id, @Status, @Last_activity, @Progress, @Created, @Agent_id)");

            dal.AddParameter("@User_id", _Application.User_id, System.Data.DbType.String);
            dal.AddParameter("@Status", _Application.Status, System.Data.DbType.String);
            dal.AddParameter("@Last_activity", _Application.Last_activity, System.Data.DbType.DateTime);
            dal.AddParameter("@Progress", _Application.Progress, System.Data.DbType.String);
            dal.AddParameter("@Created", _Application.Created, System.Data.DbType.DateTime);
            if (_Application.Agent_id != null)
            {
                dal.AddParameter("@Agent_id", _Application.Agent_id, DbType.String);
            }
            else
            {
                dal.AddParameter("@Agent_id", DBNull.Value, DbType.String);
            }

            return dal.Execute();
        }

        public IList<IApplication> GetApplicationByUserId(string _User_id)
        {
            dal.SetQuery("SELECT * FROM [application] WHERE [application].user_id = @User_id;");

            dal.AddParameter("@User_id", _User_id, System.Data.DbType.String);

            IList<IApplication> result = new List<IApplication>();
            IApplication application;
            SqlDataReader reader = dal.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    application = ReadApplication(reader);

                    result.Add(application);
                }
            }
            else return null;

            return result;
        }

        public int UpdateApplication(IApplication _Application)
        {
            dal.SetQuery("UPDATE [application] SET [status] = @Status, [last_activity] = @Last_activity, [progress] = @Progress, [created] = @Created, [education] = @Education WHERE user_id = @User_id AND created = @Created");

            dal.AddParameter("@Status", _Application.Status.ToString(), System.Data.DbType.String);
            dal.AddParameter("@Last_activity", _Application.Last_activity, System.Data.DbType.DateTime);
            dal.AddParameter("@Progress", _Application.Progress, System.Data.DbType.String);
            dal.AddParameter("@Created", _Application.Created, System.Data.DbType.DateTime);
            dal.AddParameter("@User_id", _Application.User_id, System.Data.DbType.String);
            dal.AddParameter("@Education", _Application.Education, DbType.String);
            if (_Application.Agent_id != null)
            {
                dal.AddParameter("@Agent_id", _Application.Agent_id, DbType.String);
            }
            else
            {
                dal.AddParameter("@Agent_id", DBNull.Value, DbType.String);
            }

            return dal.Execute();
        }

        public int DeleteApplication(string _User_id)
        {
            dal.SetQuery("DELETE FROM [application] WHERE [application].user_id = @User_id");
            dal.AddParameter("@User_id", _User_id, System.Data.DbType.String);
            return dal.Execute();
        }



        public int ChangeApplicationStatus(string _User_id, Status _New_status, DateTime _Created)
        {
            dal.SetQuery("UPDATE [application] SET [status] = @Status WHERE created = @Created AND user_id = @User_id");

            dal.AddParameter("@Status", _New_status.ToString(), DbType.String);
            dal.AddParameter("@User_id", _User_id, DbType.String);
            dal.AddParameter("@Created", _Created, DbType.DateTime);

            return dal.Execute();
        }

        public int UpdateApplicantPicture(string _User_id, byte[] _Data)
        {
            dal.SetQuery("UPDATE [applicant_info] SET [picture] = @Picture WHERE user_id = @User_id");

            dal.AddParameter("@Picture", _Data, SqlDbType.VarBinary, int.MaxValue);
            dal.AddParameter("@User_id", _User_id, DbType.String);

            return dal.Execute();
        }

        private IApplication ReadApplication(SqlDataReader reader)
        {
            IApplication application = new Application();

            application.User_id = reader["user_id"].ToString();
            application.Status = (Status)Enum.Parse(typeof(Status), reader["status"].ToString());
            application.Last_activity = Convert.ToDateTime(reader["last_activity"].ToString());
            application.Progress = reader["progress"].ToString();
            application.Created = Convert.ToDateTime(reader["created"].ToString());
            application.Agent_id = reader["agent_id"].ToString();
            if (reader["education"] != null)
            {
                application.Education = reader["education"].ToString();
            }

            return application;
        }

        private IApplicant ReadApplicant(SqlDataReader reader)
        {
            IApplicant applicant = new Applicant();
            IArrival arrival = new Arrival();
            IAccomodation accomodation = new Accomodation();

            applicant.Address = reader["address"].ToString();
            applicant.City = reader["city"].ToString();
            applicant.Country = reader["country"].ToString();
            applicant.Date_of_birth = Convert.ToDateTime(reader["date_of_birth"].ToString());
            applicant.Email = reader["email"].ToString();
            applicant.First_name = reader["first_name"].ToString();
            applicant.Kot_cpr = reader["kot_cpr"].ToString();
            applicant.Last_login = Convert.ToDateTime(reader["last_login"].ToString());
            applicant.Last_name = reader["last_name"].ToString();
            applicant.Sex = reader["sex"].ToString();
            applicant.Telephone_number = reader["telephone_number"].ToString();
            applicant.User_id = reader["user_id"].ToString();
            applicant.Zipcode = reader["zipcode"].ToString();
            applicant.Citizenship = reader["citizenship"].ToString();

            applicant.Has_paid_student_fee = reader["has_paid_student_fee"].ToString() == "True" ? true : false;

            if (reader["international_office_comments"] == null)
            {
                applicant.UCN_comments = "";
            }
            else
            {
                applicant.UCN_comments = reader["international_office_comments"].ToString();
            }

            if (reader["picture"].ToString() != "")
            {
                applicant.Picture = (byte[])reader["picture"];
            }
            else
            {
                applicant.Picture = null;
            }
            if (reader["flight_number"] == null || reader["flight_number"].ToString() == "")
            {
                arrival.Flight_number = "";
            }
            else
            {
                arrival.Flight_number = reader["flight_number"].ToString();
            }
            if (reader["arrival_by"].ToString() == "")
            {
                arrival.Method_of_transportation = null;
            }
            else
            {
                arrival.Method_of_transportation = (Arrival_method)Enum.Parse(typeof(Arrival_method), reader["arrival_by"].ToString());
            }
            if (reader["time_of_arrival"].ToString() == "")
            {
                arrival.Time_of_arrival = null;
            }
            else
            {
                arrival.Time_of_arrival = Convert.ToDateTime(reader["time_of_arrival"].ToString());
            }
            if (reader["accomodation_end"].ToString() == "")
            {
                accomodation.Accomodation_end = null;
            }
            else
            {
                accomodation.Accomodation_end = Convert.ToDateTime(reader["accomodation_end"].ToString());
            }
            if (reader["accomodation_start"].ToString() == "")
            {
                accomodation.Accomodation_start = null;
            }
            else
            {
                accomodation.Accomodation_start = Convert.ToDateTime(reader["accomodation_start"].ToString());
            }
            accomodation.Actual_accomodation = reader["actual_accomodation"].ToString();
            accomodation.Comments_and_wishes = reader["comments_and_wishes"].ToString();

            applicant.Accomodation = accomodation;
            applicant.Arrival = arrival;

            return applicant;
        }

        // Method name intentionally made so long :D
        public int GetApplicationCountByUserIdAndApplicationStatus(string _User_Id, Status _Status)
        {
            dal.SetQuery("SELECT COUNT(*) FROM [application] WHERE [user_id] = @pancake AND [status] = @status");
            dal.AddParameter("@pancake", _User_Id, DbType.String);
            dal.AddParameter("@status", _Status, DbType.String);
            return Convert.ToInt32(dal.ExecuteScalar());
        }

        public int UpdateApplicationCancelReason(string _User_id, string _Reason, DateTime _Created, DateTime _CancelledDate)
        {
            dal.SetQuery("INSERT INTO [cancelled_applications] (user_id, reason, application_creation_date, application_termination_date) VALUES (@User_id, @Reason, @Created, @CancelledDate");
            dal.AddParameter("@User_id", _User_id, DbType.String);
            dal.AddParameter("@Reason", _Reason, DbType.String);
            dal.AddParameter("@Created", _Created, DbType.DateTime);
            dal.AddParameter("@CancelledDate", _CancelledDate, DbType.DateTime);

            return dal.Execute();
        }

        public int SetApplicantLastLogin(string _User_id, DateTime _LastLogin)
        {
            dal.SetQuery("UPDATE [applicant_info] SET [last_login] = @Last_login WHERE [user_id] = @User_id");
            dal.AddParameter("@User_id", _User_id, DbType.String);
            dal.AddParameter("@Last_Login", _LastLogin, DbType.DateTime);

            return dal.Execute();

        }

        public IList<string> GetListOfEducations()
        {
            IList<string> result = new List<string>();
            dal.SetQuery("SELECT * FROM [education]");
            SqlDataReader reader = dal.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    result.Add((string)reader["name"]);
                }
            }
            else
            {
                return result;
            }
            return result;
        }

        public int UpdateApplicantEducation(string _User_id, string _Education, DateTime _Created)
        {
            dal.SetQuery("UPDATE [application] SET [education] = @Education WHERE [user_id] = @User_id AND [created] = @Created");
            dal.AddParameter("@User_id", _User_id, DbType.String);
            dal.AddParameter("@Created", _Created, DbType.DateTime);
            dal.AddParameter("@Education", _Education, DbType.String);

            return dal.Execute();
        }

        public IList<IApplicant> ApplicantSearch(string _ColumnName, string _SearchKeyword)
        {
            dal.SetQuery("SELECT * FROM [applicant_info] LEFT JOIN [accomodation] ON ([applicant_info].user_id = [accomodation].user_id) LEFT JOIN [arrival] ON ([applicant_info].user_id = [arrival].user_id) LEFT JOIN [application] on [applicant_info].user_id = [application].user_id WHERE [applicant_info].user_id IN (SELECT [user_id] FROM [applicant_info] WHERE [" + _ColumnName + "] LIKE @SearchKeyword)");

            dal.AddParameter("@SearchKeyword", "%" + _SearchKeyword + "%", DbType.String);
            IList<IApplicant> result = new List<IApplicant>();
            IApplicant applicant = null;
            IApplication application = null;
            int listTgt = -1;

            SqlDataReader reader = dal.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    application = null;
                    string temp = reader["status"].ToString();

                    if (!temp.Equals(""))
                    {
                        application = ReadApplication(reader);
                    }

                    listTgt = -1;
                    temp = reader["user_id"].ToString();
                    int c = result.Count;
                    for (int i = 0; i < c; i++)
                    {
                        if (result[i].User_id.Equals(temp))
                        {
                            listTgt = i;
                        }
                    }

                    if (listTgt == -1)
                    {
                        applicant = ReadApplicant(reader);

                        applicant.Aplications = new List<IApplication>();
                        if (application != null)
                        {
                            applicant.Aplications.Add(application);
                        }

                        result.Add(applicant);
                    }
                    else
                    {
                        if (application != null)
                        {
                            result[listTgt].Aplications.Add(application);
                        }
                    }
                }
            }
            else
            {
                return null;
            }
            return result;
            //IApplicant applicant;
            //if (reader.HasRows)
            //{
            //    while (reader.Read())
            //    {
            //        applicant = ReadApplicant(reader);

            //        result.Add(applicant);
            //    }
            //}
            //else return null;

            //return result;



        }


    }
}
